﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Admin.Models
{
    public class CategoriesModel
    {
        private string connectionString;
        private SqlConnection connect;

        public CategoriesModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string getCategoryByID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Categories WHERE category_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["category_name"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string[][] getAllCategories()
        {
            string [][] result;
            try
            {
                connect.Open();

                string selectSQL = "SELECT count(*) as total_category FROM Categories";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int total = 0;
                if(reader.Read())
                    total = Convert.ToInt32(reader["total_category"]);
                reader.Close();
                if (total == 0) return null;

                selectSQL = "SELECT * FROM Categories Order By category_name ASC";
                cmd = new SqlCommand(selectSQL, connect);
                reader = cmd.ExecuteReader();

                result = new string[total][];
                int i = 0;
                while(reader.Read())
                {
                    result[i] = new string[2];
                    result[i][0] = reader["category_id"].ToString();
                    result[i][1] = reader["category_name"].ToString();
                    i++;
                }

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int insertCategory(string name)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "SELECT count(*) as total_category FROM Categories WHERE category_name = N'" + name + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int total = 0;
                if (reader.Read())
                    total = Convert.ToInt32(reader["total_category"]);
                reader.Close();
                if (total != 0) return -4;

                selectSQL = "INSERT INTO Categories (category_name,created_date,updated_date) VALUES (N'" + name + "'," + Utils.Now() + "," + Utils.Now() + ")";
                cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }

        public int deleteCategory(int id)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "DELETE FROM Categories WHERE category_id = " + id.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                if (err.Message.IndexOf("FK_Book_Categories") != -1)
                    return -2;
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }

        public int editCategory(int id, string name)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "SELECT count(*) as total_category FROM Categories WHERE category_name = N'" + name + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                int total = 0;
                if (reader.Read())
                    total = Convert.ToInt32(reader["total_category"]);
                reader.Close();
                if (total != 0) return -4;

                selectSQL = "UPDATE Categories SET category_name = N'" + name + "', updated_date = " + Utils.Now() + " WHERE category_id = " + id.ToString();
                cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
    }
}
